package com.guodu.dao.impl;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.guodu.base.BaseDao;
import com.guodu.dao.SupplierBadDao;
import com.guodu.entity.SupplierBad;
import com.guodu.entity.SupplierBadStatus;
import com.guodu.entity.SupplierRedTwo;
import com.guodu.entity.Units;
import com.guodu.vo.SupplierBadQueryVo;

@Repository("supplierBadDao")
public class SupplierBadDaoImpl extends BaseDao implements SupplierBadDao {

	public int findSizeByVo(SupplierBadQueryVo supplierBadQueryVo, Units units) {
		StringBuffer hql = new StringBuffer("select count(*) from SupplierBad s where 1=1");
		if(supplierBadQueryVo != null){
			if(supplierBadQueryVo.getDayStart()!= null && !supplierBadQueryVo.getDayStart().trim().equals("")){
				 hql.append(" and s.recordingTime >= TO_DATE('"+supplierBadQueryVo.getDayStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");				 
			 }
			 if(supplierBadQueryVo.getDayEnd()!= null && !supplierBadQueryVo.getDayEnd().trim().equals("")){
				 hql.append(" and s.recordingTime <= TO_DATE('"+supplierBadQueryVo.getDayEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");				 
			 }
			 if(supplierBadQueryVo.getSupplierName() != null && !supplierBadQueryVo.getSupplierName().trim().equals("")){
				 hql.append(" and s.supplierName like '%"+supplierBadQueryVo.getSupplierName()+"%'");
			 }
			 if(supplierBadQueryVo.getSupplierCode() != null && !supplierBadQueryVo.getSupplierCode().trim().equals("")){
				 hql.append(" and s.supplierCode like '%"+supplierBadQueryVo.getSupplierCode()+"%'");
			 }
			 if(supplierBadQueryVo.getMaterial() != null && !supplierBadQueryVo.getMaterial().trim().equals("")){
				 hql.append(" and s.material like '%"+supplierBadQueryVo.getMaterial()+"%'");
			 }
			 if(supplierBadQueryVo.getIndustry() != null && !supplierBadQueryVo.getIndustry().trim().equals("")){
				 hql.append(" and s.industry like '%"+supplierBadQueryVo.getIndustry()+"%'");
			 }
			 if(supplierBadQueryVo.getCreateTimeStart() != null && !supplierBadQueryVo.getCreateTimeStart().trim().equals("")){
					hql.append(" and s.createTime >= TO_DATE('"+supplierBadQueryVo.getCreateTimeStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			 }
			 if(supplierBadQueryVo.getCreateTimeEnd() != null && !supplierBadQueryVo.getCreateTimeEnd().trim().equals("")){
					hql.append(" and s.createTime <= TO_DATE('"+supplierBadQueryVo.getCreateTimeEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			if(supplierBadQueryVo.getFunitsId() != null){
					hql.append(" and ( unitsId="+supplierBadQueryVo.getFunitsId()+" or exists(select 1 from Units u where u.unitsId = s.unitsId and u.funitsId="+supplierBadQueryVo.getFunitsId()+"))");
			}
			if(supplierBadQueryVo.getUnitsId()!=null){
					hql.append(" and  s.unitsId = "+supplierBadQueryVo.getUnitsId());
			}
			if(supplierBadQueryVo.getStatus()!=null&&supplierBadQueryVo.getStatus().trim().length()>0&&!supplierBadQueryVo.getStatus().equals("10")){
				    hql.append(" and s.status ="+supplierBadQueryVo.getStatus());
			}
	
		}
//		if(units==null){
//			hql.append("and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
//		}else
//		if(units.getUnitsLevel()==1L){
//			hql.append("and ((unitsId="+units.getUnitsId()+" or exists(select 1 from Units u where u.funitsId="+units.getUnitsId()+" and u.unitsId=s.unitsId)) or (status =1 or status=3))");
//		}else{
//			hql.append("and ((status =1 or status =3) or (unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
//		}
		if(units==null){
			hql.append("and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
		}else
		if(units.getUnitsLevel()==1L){
			hql.append("and ((unitsId="+units.getUnitsId()+" or exists(select 1 from Units u where u.funitsId="+units.getUnitsId()+" and u.unitsId=s.unitsId)))");
		}else{
			//hql.append("and ((unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
			if(supplierBadQueryVo.getUnitsId()!=null){
				hql.append(" and (exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+") or ((status =1 or status =3) and unitsId="+supplierBadQueryVo.getUnitsId()+"))");	
			}else{
				hql.append(" and (exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+") or ((status =1 or status =3) and exists(select 1 from Units us where us.unitsId = s.unitsId and us.funitsId = "+supplierBadQueryVo.getFunitsId()+")))");
			}
		}			
		return super.findInt(hql.toString());
	}

	
	public List<SupplierBad> findByVo(SupplierBadQueryVo supplierBadQueryVo,
			Units units, int curPage, int pageSize) {
		StringBuffer hql = new StringBuffer("from SupplierBad s where 1=1");
        if(supplierBadQueryVo != null){
        	if(supplierBadQueryVo.getDayStart()!= null && !supplierBadQueryVo.getDayStart().trim().equals("")){
				 hql.append(" and s.recordingTime >= TO_DATE('"+supplierBadQueryVo.getDayStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");				 
			 }
			 if(supplierBadQueryVo.getDayEnd()!= null && !supplierBadQueryVo.getDayEnd().trim().equals("")){
				 hql.append(" and s.recordingTime <= TO_DATE('"+supplierBadQueryVo.getDayEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");				 
			 }
			 if(supplierBadQueryVo.getSupplierName() != null && !supplierBadQueryVo.getSupplierName().trim().equals("")){
				 hql.append(" and s.supplierName like '%"+supplierBadQueryVo.getSupplierName()+"%'");
			 }
			 if(supplierBadQueryVo.getSupplierCode() != null && !supplierBadQueryVo.getSupplierCode().trim().equals("")){
				 hql.append(" and s.supplierCode like '%"+supplierBadQueryVo.getSupplierCode()+"%'");
			 }
			 if(supplierBadQueryVo.getMaterial() != null && !supplierBadQueryVo.getMaterial().trim().equals("")){
				 hql.append(" and s.material like '%"+supplierBadQueryVo.getMaterial()+"%'");
			 }
			 if(supplierBadQueryVo.getIndustry() != null && !supplierBadQueryVo.getIndustry().trim().equals("")){
				 hql.append(" and s.industry like '%"+supplierBadQueryVo.getIndustry()+"%'");
			 }
			 if(supplierBadQueryVo.getCreateTimeStart() != null && !supplierBadQueryVo.getCreateTimeStart().trim().equals("")){
					hql.append(" and s.createTime >= TO_DATE('"+supplierBadQueryVo.getCreateTimeStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			 }
			 if(supplierBadQueryVo.getCreateTimeEnd() != null && !supplierBadQueryVo.getCreateTimeEnd().trim().equals("")){
					hql.append(" and s.createTime <= TO_DATE('"+supplierBadQueryVo.getCreateTimeEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			 if(supplierBadQueryVo.getFunitsId() != null){
					hql.append(" and ( unitsId="+supplierBadQueryVo.getFunitsId()+" or exists(select 1 from Units u where u.unitsId = s.unitsId and u.funitsId="+supplierBadQueryVo.getFunitsId()+"))");
			}
			if(supplierBadQueryVo.getUnitsId()!=null){
					hql.append(" and  unitsId = "+supplierBadQueryVo.getUnitsId());
			}
			if(supplierBadQueryVo.getStatus()!=null&&supplierBadQueryVo.getStatus().trim().length()>0&&!supplierBadQueryVo.getStatus().equals("10")){
			    hql.append(" and status ="+supplierBadQueryVo.getStatus());
		    }
		}
//        if(units==null){
//			hql.append("and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
//		}else
//		if(units.getUnitsLevel()==1L){
//			hql.append("and ((unitsId="+units.getUnitsId()+" or exists(select 1 from Units u where u.funitsId="+units.getUnitsId()+" and u.unitsId=s.unitsId)) or (status =1 or status=3))");
//		}else{
//			hql.append("and ((status =1 or status =3) or (unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
//		}
        if(units==null){
			hql.append("and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
		}else
		if(units.getUnitsLevel()==1L){
			hql.append("and ((unitsId="+units.getUnitsId()+" or exists(select 1 from Units u where u.funitsId="+units.getUnitsId()+" and u.unitsId=s.unitsId)))");
		}else{
			//hql.append("and ((unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
			if(supplierBadQueryVo.getUnitsId()!=null){
				hql.append(" and (exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+") or ((status =1 or status =3) and unitsId="+supplierBadQueryVo.getUnitsId()+"))");	
			}else{
				hql.append(" and (exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+") or ((status =1 or status =3) and exists(select 1 from Units us where us.unitsId = s.unitsId and us.funitsId = "+supplierBadQueryVo.getFunitsId()+")))");
			}
		}	   
        hql.append(" order by createTime desc");			
		return super.findPages(hql.toString(), curPage, pageSize);
	}


	@SuppressWarnings("unchecked")
	public List<SupplierBad> downLoad(SupplierBadQueryVo supplierBadQueryVo,Units units) {
		StringBuffer hql = new StringBuffer("from SupplierBad s where 1=1");
        if(supplierBadQueryVo != null){
        	if(supplierBadQueryVo.getDayStart()!= null && !supplierBadQueryVo.getDayStart().trim().equals("")){
				 hql.append(" and s.recordingTime >= TO_DATE('"+supplierBadQueryVo.getDayStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");				 
			 }
			 if(supplierBadQueryVo.getDayEnd()!= null && !supplierBadQueryVo.getDayEnd().trim().equals("")){
				 hql.append(" and s.recordingTime <= TO_DATE('"+supplierBadQueryVo.getDayEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");				 
			 }
			 if(supplierBadQueryVo.getSupplierName() != null && !supplierBadQueryVo.getSupplierName().trim().equals("")){
				 hql.append(" and s.supplierName like '%"+supplierBadQueryVo.getSupplierName()+"%'");
			 }
			 if(supplierBadQueryVo.getSupplierCode() != null && !supplierBadQueryVo.getSupplierCode().trim().equals("")){
				 hql.append(" and s.supplierCode like '%"+supplierBadQueryVo.getSupplierCode()+"%'");
			 }
			 if(supplierBadQueryVo.getMaterial() != null && !supplierBadQueryVo.getMaterial().trim().equals("")){
				 hql.append(" and s.material like '%"+supplierBadQueryVo.getMaterial()+"%'");
			 }
			 if(supplierBadQueryVo.getIndustry() != null && !supplierBadQueryVo.getIndustry().trim().equals("")){
				 hql.append(" and s.industry like '%"+supplierBadQueryVo.getIndustry()+"%'");
			 }
			 if(supplierBadQueryVo.getCreateTimeStart() != null && !supplierBadQueryVo.getCreateTimeStart().trim().equals("")){
					hql.append(" and s.createTime >= TO_DATE('"+supplierBadQueryVo.getCreateTimeStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			 }
			 if(supplierBadQueryVo.getCreateTimeEnd() != null && !supplierBadQueryVo.getCreateTimeEnd().trim().equals("")){
					hql.append(" and s.createTime <= TO_DATE('"+supplierBadQueryVo.getCreateTimeEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			 if(supplierBadQueryVo.getFunitsId() != null){
					hql.append(" and ( unitsId="+supplierBadQueryVo.getFunitsId()+" or exists(select 1 from Units u where u.unitsId = s.unitsId and u.funitsId="+supplierBadQueryVo.getFunitsId()+"))");
			}
			if(supplierBadQueryVo.getUnitsId()!=null){
					hql.append(" and  unitsId = "+supplierBadQueryVo.getUnitsId());
			}
			if(supplierBadQueryVo.getStatus()!=null&&supplierBadQueryVo.getStatus().trim().length()>0&&!supplierBadQueryVo.getStatus().equals("10")){
			    hql.append(" and status ="+supplierBadQueryVo.getStatus());
		    }
		}
//        if(units==null){
//			hql.append("and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
//		}else
//		if(units.getUnitsLevel()==1L){
//			hql.append("and ((unitsId="+units.getUnitsId()+" or exists(select 1 from Units u where u.funitsId="+units.getUnitsId()+" and u.unitsId=s.unitsId)) or (status =1 or status=3))");
//		}else{
//			hql.append("and ((status =1 or status =3) or (unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
//		}
        if(units==null){
			hql.append("and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
		}else
		if(units.getUnitsLevel()==1L){
			hql.append("and ((unitsId="+units.getUnitsId()+" or exists(select 1 from Units u where u.funitsId="+units.getUnitsId()+" and u.unitsId=s.unitsId)))");
		}else{
			hql.append("and ((unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
		}        
		hql.append(" order by createTime desc");		
		return super.find(hql.toString());
	}

	
	public void examinAll(SupplierBadQueryVo supplierBadQueryVo, Units units) {
		StringBuffer hql = new StringBuffer();
        if(units==null){
        	hql.append("update from SupplierBad s set s.status=3 where s.status=0 ");
		}else if(units.getUnitsLevel()==1L){
			hql.append("update from SupplierBad s set s.status=1 where s.status=0 ");
		}
        if(supplierBadQueryVo != null){
        	if(supplierBadQueryVo.getDayStart()!= null && !supplierBadQueryVo.getDayStart().trim().equals("")){
				 hql.append(" and s.recordingTime >= TO_DATE('"+supplierBadQueryVo.getDayStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");				 
			 }
			 if(supplierBadQueryVo.getDayEnd()!= null && !supplierBadQueryVo.getDayEnd().trim().equals("")){
				 hql.append(" and s.recordingTime <= TO_DATE('"+supplierBadQueryVo.getDayEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");				 
			 }
			 if(supplierBadQueryVo.getSupplierName() != null && !supplierBadQueryVo.getSupplierName().trim().equals("")){
				 hql.append(" and s.supplierName like '%"+supplierBadQueryVo.getSupplierName()+"%'");
			 }
			 if(supplierBadQueryVo.getSupplierCode() != null && !supplierBadQueryVo.getSupplierCode().trim().equals("")){
				 hql.append(" and s.supplierCode like '%"+supplierBadQueryVo.getSupplierCode()+"%'");
			 }
			 if(supplierBadQueryVo.getMaterial() != null && !supplierBadQueryVo.getMaterial().trim().equals("")){
				 hql.append(" and s.material like '%"+supplierBadQueryVo.getMaterial()+"%'");
			 }
			 if(supplierBadQueryVo.getIndustry() != null && !supplierBadQueryVo.getIndustry().trim().equals("")){
				 hql.append(" and s.industry like '%"+supplierBadQueryVo.getIndustry()+"%'");
			 }
			 if(supplierBadQueryVo.getCreateTimeStart() != null && !supplierBadQueryVo.getCreateTimeStart().trim().equals("")){
					hql.append(" and s.createTime >= TO_DATE('"+supplierBadQueryVo.getCreateTimeStart()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			 }
			 if(supplierBadQueryVo.getCreateTimeEnd() != null && !supplierBadQueryVo.getCreateTimeEnd().trim().equals("")){
					hql.append(" and s.createTime <= TO_DATE('"+supplierBadQueryVo.getCreateTimeEnd()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			 if(supplierBadQueryVo.getFunitsId() != null){
					hql.append(" and ( unitsId="+supplierBadQueryVo.getFunitsId()+" or exists(select 1 from Units u where u.unitsId = s.unitsId and u.funitsId="+supplierBadQueryVo.getFunitsId()+"))");
			}
			if(supplierBadQueryVo.getUnitsId()!=null){
					hql.append(" and  unitsId = "+supplierBadQueryVo.getUnitsId());
			}
			if(supplierBadQueryVo.getStatus()!=null&&supplierBadQueryVo.getStatus().trim().length()>0&&!supplierBadQueryVo.getStatus().equals("10")){
			    hql.append(" and status ="+supplierBadQueryVo.getStatus());
		    }
		}

        if(units==null){
			hql.append("and (s.status=1 or exists(select 1 from UserInfo u where s.createPeople = u.userId and exists(select 1 from Units n where n.unitsLevel=1 and n.unitsId = u.unitsId)))");
		}else
		if(units.getUnitsLevel()==1L){
			hql.append("and ((unitsId="+units.getUnitsId()+" or exists(select 1 from Units u where u.funitsId="+units.getUnitsId()+" and u.unitsId=s.unitsId)))");
		}else{
			hql.append("and ((unitsId="+units.getUnitsId()+" and exists(select 1 from UserInfo u where u.userId = s.createPeople and unitsId="+units.getUnitsId()+")))");
		}  
        super.executeUpdate(hql.toString());
		
	}

	public void save(SupplierBad supplierBad) {
		super.save(supplierBad);
		
	}

	public void examine(String ids, Long status) {
		StringBuffer hql = new StringBuffer("update SupplierBad set status =");
		hql.append(status);
		hql.append(" where badId in (");
		hql.append(ids);
		hql.append(")");
		super.executeUpdate(hql.toString());
		
	}
	public void delByIds(String[] ids) {
		super.deleteAllById(SupplierBad.class,ids);
		
	}

	public SupplierBad findById(String id) {
		
		return (SupplierBad) super.findById(SupplierBad.class,id);
	}


	
	public void update(SupplierBad supplierBad) {
		super.update(supplierBad);
		
	}

	public void changeStatus(List<SupplierBadStatus> supplierBadStatusList) {
		super.saveAll(supplierBadStatusList);
		
	}


	
	public List<SupplierBadStatus> findStatus(String id) {
		
		return super.findByProperty(SupplierBadStatus.class,"badId",id);
	}

	public void deleteById(String id) {
		super.delete(SupplierBad.class, id);
		
	}




	
	

}
